package informationplatform.product

import informationplatform.User;
import informationplatform.milestone.Milestone;
import informationplatform.milestone.MilestoneFunction;
import informationplatform.milestone.MilestoneFunctionPartake;
import informationplatform.milestone.MilestoneFunctionType;

import java.io.File;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.CellStyle
import org.apache.poi.ss.usermodel.Font
import org.apache.poi.ss.usermodel.IndexedColors
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.ss.util.CellRangeAddress
import org.codehaus.groovy.grails.exceptions.GrailsRuntimeException


class MigrationService {
	static transactional = true
	DecimalFormat df = new DecimalFormat("#.#");
	static SimpleDateFormat dateFormat = new SimpleDateFormat('yyyy.MM.dd')
	def milestoneFunctionService
	def productHistoryService
	static final String USERNM ="李晨"
	/**
	 * 函数入口, 举出给定目录的表格文件
	 * 
	 * @return
	 */
	def migrationMain(){
		File folder = new File("/var/guidepost-migration-doc/2014年度OTNM2000路标");
		log.info("你当前的工作目录是：${folder.getAbsolutePath()}")

		def excelFileList = [];

		File[] fileList = folder.listFiles();
		for (File file : fileList) {
			if (!file.isDirectory() && (file.getName().endsWith("xlsx") || file.getName().endsWith("xls"))) {
				excelFileList.add(file);
				log.info("excel file: ${file.getName()}")
			}
		}

		for(File file: excelFileList){
			migrationFile(file)
			return
		}
	}

	/**
	 * 读取单个文件
	 * 
	 * @param file
	 * @return
	 */
	def migrationFile(File file){
		FileInputStream is
		try{
			// 创建一个文件输入流
			is = new FileInputStream(file);
			// 得到工作薄
			Workbook workbook = new XSSFWorkbook(is);
			// 得到工作表的张数
			int num = workbook.getNumberOfSheets();
			log.info("----------------- ${file.getName()}有: ${num} 个Sheet-----------------START")

			Sheet pversionSheet = workbook.getSheetAt(0);
			def pversion = migrationPversion(pversionSheet)

			Sheet milestoneSheet = workbook.getSheetAt(1);
			migrationMilestones(milestoneSheet, pversion)

			Sheet functionSheet = workbook.getSheetAt(2);
			migrationFunction(functionSheet, pversion, null)


			generateAutoFunction(pversion)

		}catch(Exception e){
			log.error(e.getMessage(), e)
			throw e
		}finally{
			is.close();
		}
	}

	/**
	 * 构建版本
	 * @param pversionSheet
	 * @return
	 */
	def migrationPversion(Sheet sheet){
		log.info("开始解析版本......")
		//--------------- 定义版本所有属性---------------start
		def properties=[:]

		//--------------- 定义版本所有属性---------------end

		int lastRowNm= sheet.getLastRowNum()
		int firstRowNm= sheet.getFirstRowNum()

		//逐行解析
		for(int rowNm=firstRowNm; rowNm <=lastRowNm; rowNm++){
			//逐列解析
			for(int colNm=0; colNm<7;colNm++){
				def cell = sheet.getRow(rowNm).getCell(colNm)
				if(!cell || cell.getCellType() != Cell.CELL_TYPE_STRING){
					continue
				}


				def cellValue = cell.getStringCellValue().trim()//取值

				if('软件类型'.equals(cellValue) && !properties.containsKey('guidepostName')){
					properties << [guidepostName: sheet.getRow(rowNm+2).getCell(colNm)?.getStringCellValue()?.trim()]
					log.info("guidepostName=${properties.guidepostName}")
				}

				if('软件版本'.equals(cellValue) && !properties.containsKey('name')){
					properties << [name: sheet.getRow(rowNm+2).getCell(colNm)?.getStringCellValue()?.trim()]
					log.info("name=${properties.name}")
				}

				if('版本评审'.equals(cellValue)){
					properties << [planCompleteDate: sheet.getRow(rowNm).getCell(colNm+1)?.getStringCellValue()?.trim()?:'2012.1.1']
					log.info("planCompleteDate=${properties.planCompleteDate}")

					properties.planCompleteDate = dateFormat.parse(properties.planCompleteDate)
				}

				if('提交中试'.equals(cellValue)){
					properties << [pilotscaleExperimentDate: sheet.getRow(rowNm).getCell(colNm+1)?.getStringCellValue()?.trim()]
					log.info("pilotscaleExperimentDate=${properties.pilotscaleExperimentDate}")

					properties.pilotscaleExperimentDate = dateFormat.parse(properties.pilotscaleExperimentDate)
				}

				if('版本发布与释放'.equals(cellValue)){
					properties << [realeseDate: sheet.getRow(rowNm).getCell(colNm+1)?.getStringCellValue()?.trim()]
					log.info("realeseDate=${properties.realeseDate}")

					properties.realeseDate = dateFormat.parse(properties.realeseDate)
				}

				if('版本功能说明'.equals(cellValue)){
					properties << [functionDesc: sheet.getRow(rowNm).getCell(colNm+1)?.getStringCellValue()?.trim()]
					log.info("functionDesc=${properties.functionDesc}")
				}

				if('版本适用范围'.equals(cellValue)){
					properties << [scopeOfApplicationDesc: sheet.getRow(rowNm).getCell(colNm+1)?.getStringCellValue()?.trim()]
					log.info("scopeOfApplicationDesc=${properties.scopeOfApplicationDesc}")
				}
			}
		}

		def guidepost = ProductGuidepost.findByName('2014年度OTNM2000路标')
		def pversion = new ProductVersion()
		pversion.properties = properties
		pversion.guidepost = guidepost
		pversion.product = guidepost.product
		pversion.status = GuidepostStatus.CREATED
		pversion.direction = "版本去向"

		def user = User.findByRealName(USERNM)
		pversion.creator = user
		pversion.director = user

		pversion.save()
		validateSave(pversion)
		productHistoryService.logNotupdateHistory(pversion, false)//记录新建日志

		return pversion
	}

	//迁移里程碑
	def migrationMilestones(Sheet sheet, pversion){
		log.info("开始解析里程碑......")

		int firstRowNm= sheet.getFirstRowNum()
		int lastRowNm= sheet.getLastRowNum()
		int firstColNm = sheet.getRow(0).getFirstCellNum();
		int lastColNm = sheet.getRow(0).getLastCellNum();
		log.info("Sheet可视范围: (${firstRowNm}, ${lastRowNm}), (${firstColNm}, ${lastColNm})")

		int regionNum= sheet.getNumMergedRegions()//里程碑的个数
		log.info("里程碑不会超过 ${regionNum} 个")

		//int no=1//里程碑序号
		for(int i=0; i<=regionNum-1; i++){
			def region = sheet.getMergedRegion(i);
			log.info("疑似里程碑的合并单元格: " + region.formatAsString())

			def cell = sheet.getRow(region.getFirstRow()).getCell(region.getFirstColumn())
			if(!cell || cell.getCellType() != Cell.CELL_TYPE_STRING){
				continue
			}

			def cellValue = cell.getStringCellValue().trim()//取值
			if(cellValue.startsWith('里程碑')){
				log.info("开始解析里程碑 name=${cellValue}")
				def no = cellValue.substring(3,4).toInteger()
				migrationOneMilestone(sheet, region, no, cellValue , pversion)
				//no++
			}
		}
	}

	/**
	 * 解析一个里程碑
	 * 
	 * @param sheet
	 * @param region
	 * @param no 里程碑序号
	 * @return
	 */
	def migrationOneMilestone(sheet, region, no, name, pversion){

		int firstRowNm= region.getFirstRow()
		int lastRowNm= region.getLastRow()
		int firstColNm = region.getLastColumn() + 1;
		int lastColNm = sheet.getRow(0).getLastCellNum();
		log.info("里程碑的时间属性范围: (${firstRowNm}, ${firstColNm}), (${lastRowNm}, ${lastColNm})")


		def properties=[:]//存储属性
		properties.no= no
		properties.name = name

		for(int i=firstRowNm; i<=lastRowNm; i++){
			def cell = sheet.getRow(i).getCell(firstColNm)
			if(!cell || cell.getCellType() != Cell.CELL_TYPE_STRING){
				continue
			}


			def cellValue = cell.getStringCellValue().trim()//取值


			if('版本评审'.equals(cellValue)){
				def dateValue =sheet.getRow(i).getCell(firstColNm+1)?.getStringCellValue()?.trim()
				if(!dateValue || dateValue=="")
					continue

				log.info("planReviewCompleteDate=${dateValue}")
				properties << [planReviewCompleteDate: dateFormat.parse(dateValue)]
			}

			if('测试方案评审'.equals(cellValue)){
				def dateValue =sheet.getRow(i).getCell(firstColNm+1)?.getStringCellValue()?.trim()
				if(!dateValue || dateValue==""|| dateValue=="无")
					continue

				log.info("planTestplanReviewCompleteDate=${dateValue}")
				properties << [planTestplanReviewCompleteDate: dateFormat.parse(dateValue)]
			}

			if(cellValue.endsWith("自测")){
				def dateValue =sheet.getRow(i).getCell(firstColNm+1)?.getStringCellValue()?.trim()
				if(!dateValue || dateValue=="" || dateValue=="无")
					continue

				log.info("planDevelopCompleteDate=${dateValue}")
				properties << [planDevelopCompleteDate: dateFormat.parse(dateValue)]
			}

			if(cellValue.endsWith("开发测试")){
				def dateValue =sheet.getRow(i).getCell(firstColNm+1)?.getStringCellValue()?.trim()
				if(!dateValue || dateValue==""|| dateValue=="无")
					continue

				log.info("planTestCompleteDate=${dateValue}")
				properties << [planTestCompleteDate: dateFormat.parse(dateValue)]
			}

			if(cellValue.endsWith("运维测试")|| cellValue.endsWith("测试专项测试")|| cellValue.endsWith("团队测试")){
				def dateValue =sheet.getRow(i).getCell(firstColNm+1)?.getStringCellValue()?.trim()
				if(!dateValue || dateValue==""|| dateValue=="无")
					continue

				log.info("planSystemTestCompleteDate=${dateValue}")
				properties << [planSystemTestCompleteDate: dateFormat.parse(dateValue)]
			}
		}

		def milestone = new Milestone()
		milestone.properties = properties

		milestone.description=name
		milestone.startDate = milestone.planReviewCompleteDate

		if(milestone.planSystemTestCompleteDate){
			milestone.endDate = milestone.planSystemTestCompleteDate

		}else if(milestone.planTestCompleteDate){
			milestone.endDate = milestone.planTestCompleteDate

		}else if(milestone.planDevelopCompleteDate){
			milestone.endDate = milestone.planDevelopCompleteDate

		}

		milestone.reviewCnt = 0

		milestone.product = pversion.product
		milestone.guidepost = pversion.guidepost
		milestone.pversion=pversion
		milestone.status = GuidepostStatus.CREATED

		milestone.save()

		validateSave(milestone)
		productHistoryService.logNotupdateHistory(milestone, false)//记录新建日志
	}

	/**
	 * 迁移功能项
	 * @param sheet
	 * @return
	 */
	def migrationFunction(Sheet sheet, pversion, prefix){
		log.info("开始解析功能项......")

		int firstRowNm= sheet.getFirstRowNum()
		int lastRowNm= sheet.getLastRowNum()
		int firstColNm = sheet.getRow(0).getFirstCellNum();
		int lastColNm = sheet.getRow(0).getLastCellNum();
		Integer readFromColNm= null
		Integer readToColNm= 0

		log.info("firstRowNm = ${firstRowNm}, lastRowNm=${lastRowNm}, firstColNm=${firstColNm}, lastColNm=${lastColNm}")
		
		def columnMapping = [:]
		for(int i=0; i<lastColNm; i++){
			//从第3行开始
			def cell = sheet.getRow(2).getCell(i)
			if(!cell || cell.getCellType() != Cell.CELL_TYPE_STRING){
				break
			}

			def cellValue = cell.getStringCellValue().trim()//取值
			if(cellValue==null || cellValue==""){
				break
			}

			if(readFromColNm==null){
				readFromColNm = i
			}
			readToColNm = i

			columnMapping."${i}"=cellValue
		}

		log.info("主内容范围: (4, ${readFromColNm}), (${lastRowNm}, ${readToColNm})")
		log.info("所有列 = ${columnMapping}")

		//从第5行开始
		def bigType = ""//要影响下一行

		for(int rowNm=5; rowNm<lastRowNm;rowNm++){
			log.info("------------------ 第 ${rowNm}行-------------------")

			def row = sheet.getRow(rowNm)

			//--------------------- 取序号,试探第一列-------------------START
			def cell = row.getCell(readFromColNm)
			if(!cell){
				break
			}
			def funcNo = cell.getNumericCellValue()
			if(!funcNo){
				break
			}
			//--------------------- 取序号-------------------END

			def properties=[:]//存储属性
			def milestone
			def dataValueOfPartake
			def dataValueOfDirector
			def dataValueOfDirectorGroup

			//每一行从序号列后一列开始读取, 读到最后有效列
			for(int colNm=readFromColNm+1; colNm <= readToColNm; colNm++){
				def head =columnMapping."${colNm}"
				cell = row.getCell(colNm)
				log.info("------------------ 第 ${colNm}列: ${head}-------------------")


				def dataValue=null
				if(cell){
					if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
						dataValue =cell.getNumericCellValue()
						if(dataValue instanceof Double){
							dataValue = dataValue.toInteger()
						}

					}else{
						dataValue =cell.getStringCellValue()?.trim()
					}
				}


				if(head.equals("功能大类")){
					if(dataValue && dataValue!=""){
						bigType = dataValue
					}
				}


				if(!dataValue || dataValue.equals('无'))
					continue


				if(head.equals("更改类型")){
					properties.type = dataValue
					continue
				}

				if(head.equals("功能说明")){
					properties.description = dataValue
					continue
				}

				if(head.equals("里程碑")){
					def no = getIntegerNo(dataValue)
					if(!no){
						continue
					}

					milestone =  Milestone.withCriteria(uniqueResult: true){
						eq('pversion',pversion)
						eq('no',no)
					}
					
					continue
				}

				if(head.equals("需求case")){
					properties.requirementOrigin = dataValue
					def link = cell.getHyperlink()
					if(link){
						//properties.requirementOrigin = link.getLabel()
						properties.requirementOriginLink = link.getAddress()
					}
					
					continue
				}

				if(head.equals("需求来源")){
					properties.requirementOrigin = dataValue
					continue
				}

				if(head.equals("评审级别")){
					properties.reviewLevel = dataValue
					continue
				}

				if(head.equals("是否提供需求文档")){
					properties.needSupportRequirementDoc = ("是".equals(dataValue))?true:false
					continue
				}

				if(head.endsWith("文档类型")){
					properties.supportDesignDocType = dataValue
					continue
				}

				if(head.endsWith("antis")){
					properties.testCase = dataValue

					def link = cell.getHyperlink()
					if(link){
						//properties.testCase = link.getLabel()
						properties.testCaseLink = link.getAddress()
					}
					
					continue
				}

				if(head.endsWith("开发完成时间")){
					properties.planCompleteDate = dateFormat.parse(dataValue)
					properties.requirementPromiseCompleteDate = properties.planCompleteDate
					
					continue
				}

				if(head.equals("需求承诺完成时间")){
					properties.requirementPromiseCompleteDate = dateFormat.parse(dataValue)
					continue
				}

				if(head.endsWith("单元测试")){
					properties.needUnitTesting =("是".equals(dataValue))?true:false
					continue
				}

				if(head.endsWith("代码审查")){
					properties.needCheckCode =("是".equals(dataValue))?true:false
					continue
				}

				if(head.endsWith("需要评审")){
					properties.needReviewDesignDoc =("是".equals(dataValue))?true:false
					continue
				}

				if(head.endsWith("原型")){
					properties.needSupportPrototype =("是".equals(dataValue))?true:false
					continue
				}

				//'止时间'  = 版本评审时间
				properties.startDate = pversion.planCompleteDate

				//解析参与专项
				if(head.startsWith("参与专项")){
					dataValueOfPartake = dataValue
					continue
				}
				//解析负责人
				if(head.startsWith("负责人")){
					dataValueOfDirector = dataValue
					continue
				}

				//解析负责专项
				if(head.startsWith("负责专项")){
					dataValueOfDirectorGroup = dataValue
					continue
				}
			}

			if(!properties.planCompleteDate || ! milestone){
				continue
			}

			def milestoneFunc = new MilestoneFunction()
			properties.bigType = bigType
			milestoneFunc.properties = properties
			milestoneFunc.name = "${prefix?(prefix+'_'):''}功能项_${funcNo?funcNo.toInteger():''}"
			milestoneFunc.product = pversion.product
			milestoneFunc.guidepost = pversion.guidepost
			milestoneFunc.pversion = pversion
			milestoneFunc.milestone = milestone
			milestoneFunc.functionType = MilestoneFunctionType.USER_FUNCTION
			milestoneFunc.status = GuidepostStatus.CREATED

			def creator = User.findByRealName(USERNM)
			milestoneFunc.creator = creator

			milestoneFunc.save()
			validateSave(milestoneFunc)
			productHistoryService.logNotupdateHistory(milestoneFunc, false)//记录新建日志

			//参与专项
			if(dataValueOfPartake){
				def partakeGroups = dataValueOfPartake.split("、")
				partakeGroups.each{
					def user = User.findByRealName(it)
					if(user){
						def partake = new MilestoneFunctionPartake()
						partake.leaderOfpartake = user
						partake.groupOfPartake= user.group
						partake.isMain = false
						partake.milestoneFunction = milestoneFunc
						partake.save()
						validateSave(partake)
					}
				}
			}

			//负责人
			if(dataValueOfDirector){
				def user = User.findByRealName(dataValueOfDirector)

				if(!user && dataValueOfDirectorGroup){
					user = User.findByRealName(dataValueOfDirectorGroup)
				}

				if(user){
					def partake = new MilestoneFunctionPartake()
					partake.leaderOfpartake = user
					partake.groupOfPartake= user.group
					partake.isMain = true
					partake.milestoneFunction = milestoneFunc
					partake.save()

					validateSave(partake)
				}
			}
		}
	}


	private generateAutoFunction(pversion){
		def milestones = Milestone.withCriteria{ eq('pversion', pversion) }
		def user = User.findByRealName(USERNM)
		def autoFunc

		milestones.each{milestone ->

			autoFunc = milestoneFunctionService.createVersionAuditFunction(pversion.product, pversion.guidepost, pversion, milestone, user.group, user)
			validateSave(autoFunc)

			if (milestone.planTestplanReviewCompleteDate) {//交给测试专项
				autoFunc = milestoneFunctionService.createTestPlanAuditFunction(pversion.product, pversion.guidepost, pversion, milestone, user)
				validateSave(autoFunc)
			}
			if (milestone.planTestCompleteDate) {//交给测试专项
				autoFunc = milestoneFunctionService.createTestFunction(pversion.product, pversion.guidepost, pversion, milestone, user)
				validateSave(autoFunc)
			}
			if (milestone.planSystemTestCompleteDate) {//网管产品管理与测试团队
				autoFunc = milestoneFunctionService.createSystemTestFunction(pversion.product, pversion.guidepost, pversion, milestone, user)
				validateSave(autoFunc)
			}
		}
	}

	private validateSave(obj){
		if(obj.errors.hasErrors()){
			def errorMsg=''
			obj.errors.allErrors.each { errorMsg += it  }

			log.error(errorMsg)
			throw new GrailsRuntimeException(errorMsg)
		}
	}

	private getIntegerNo(dataValue){
		def re
		try{
			re = dataValue.toInteger()
		}catch(Exception e){
			re = null
		}

		return re
	}
}
